package com.origin.onlinesys.service.modules.poi;


import com.origin.onlinesys.common.services.PoiUtils;
import com.origin.onlinesys.domain.modules.interviewsys.InterviewForeignKey;
import com.origin.onlinesys.domain.modules.interviewsys.InterviewerInfo;
import com.origin.onlinesys.domain.modules.interviewsys.InterviewerTrace;
import com.origin.onlinesys.persistence.modules.interviewsys.InterviewerInfoMapper;
import com.origin.onlinesys.persistence.modules.interviewsys.InterviewerTraceMapper;
import com.origin.onlinesys.service.modules.interviewsys.impl.InterviewerInfoServiceImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Pattern;

/**
 * Created by Administrator on 2015/3/10 0010.
 */
@Service
public class InterviewInfoAndTracePoi{

    @Autowired
    private InterviewerInfoMapper infoMapper;
    @Autowired
    private InterviewerTraceMapper traceMapper;

    @Autowired
    private InterviewerInfoServiceImpl service;
    private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    public Object insertInterviewByXls(String path,String zoneId,int time){
        //获得excel的数据
        List<List<String>> xlsList = new PoiUtils().analyzeXls(1,path);
        //需要用的外表数据
        List<InterviewForeignKey> foreignKeys = infoMapper.selectForeginKey(zoneId);
        Map<String,String> keyMap = new HashMap<String,String>();
        for(InterviewForeignKey bean:foreignKeys){keyMap.put(bean.getName(),bean.getId());}

        //导入的电话号码
        List<String> addPhoneList = new ArrayList<String>();
        for(List<String> list:xlsList){addPhoneList.add(list.get(2));}

        //数据库中的电话号码
        List<InterviewerInfo> phoneList = infoMapper.selectTimeOut(addPhoneList);
        Map<String,String> phoneMap = new HashMap<String,String>();
        Map<String,String> phoneCreateTimeMap = new HashMap<String,String>();
        for(InterviewerInfo bean:phoneList){
            phoneMap.put(bean.getPhoneNum(),bean.getId());phoneCreateTimeMap.put(bean.getPhoneNum(),bean.getCreateTime());
        }

        //excel必要数据的验证
        List<String> valiList = this.validateExcel(xlsList,keyMap);
        if(valiList.size()>0) return valiList;

        List<InterviewerInfo> addDbList = new ArrayList<InterviewerInfo>();
        InterviewerInfo info = null;
        long cha;
        for(List<String> list:xlsList){
            info = new InterviewerInfo();
            info.setCreateTime(list.get(0));
            info.setRealName(list.get(1));
            info.setPhoneNum(list.get(2));
            info.setEduId(keyMap.get(list.get(3)));
            info.setSourceId(keyMap.get(list.get(4)));
            info.setUserId(keyMap.get(list.get(5)));
            if(phoneMap.get(list.get(2)) == null){
                addDbList.add(info);
            }else{
                try {
                    cha = sdf.parse(list.get(0)).getTime() - sdf.parse(phoneCreateTimeMap.get(list.get(2))).getTime();
                    if(cha >= 1000*60*60*24*time){
                        info.setId(phoneMap.get(list.get(2)));
                        service.updateInterv(info);
                    }
                } catch (ParseException e) {
                    e.printStackTrace();
                }
            }
        }
        if(addDbList.size()>0) infoMapper.addBatch(addDbList);

        //重新从数据库中取电话号码
        phoneList = infoMapper.selectTimeOut(addPhoneList);
        phoneMap = new HashMap<String,String>();
        for(InterviewerInfo bean:phoneList){phoneMap.put(bean.getPhoneNum(),bean.getId());}

        //添加的追踪记录
        Map<String,String> traceMap = new HashMap<String,String>();
        List<InterviewerTrace> traceList = traceMapper.selectTimeOut(addPhoneList);
        StringBuilder sb;
        for(InterviewerTrace trace:traceList){
            if(trace.getVisitTime() != null){ sb = new StringBuilder(trace.getVisitTime()).append(trace.getNote());
            traceMap.put(sb.toString(),"true");}
        }

        List<InterviewerTrace> addDbTraceList = new ArrayList<InterviewerTrace>();
        for(List<String> list:xlsList){
            sb = new StringBuilder(list.get(6) + ".0").append(list.get(2));
            if(traceMap.get(sb.toString()) == null && list.get(6) != null){
                addDbTraceList.add(this.getTraceInExcel(list,phoneMap,keyMap,6));
            }
            sb = new StringBuilder(list.get(9)+".0").append(list.get(2));
            if(traceMap.get(sb.toString()) == null && list.get(9) != null){
                addDbTraceList.add(this.getTraceInExcel(list, phoneMap, keyMap, 9));
            }
            sb = new StringBuilder(list.get(12)+".0").append(list.get(2));
            if(traceMap.get(sb.toString()) == null && list.get(12) != null){
                addDbTraceList.add(this.getTraceInExcel(list, phoneMap, keyMap, 12));
            }
        }
        if(addDbTraceList.size()>0) traceMapper.addBatch(addDbTraceList);
        return 1;
    }

    private InterviewerTrace getTraceInExcel(List<String> list,Map<String,String> phoneMap,Map<String,String> keyMap,int i){
        InterviewerTrace trace = new InterviewerTrace();
        trace.setIntervId(phoneMap.get(list.get(2)));
        trace.setUserId(keyMap.get(list.get(5)));
        trace.setVisitTime(list.get(i));
        trace.setSituId(keyMap.get(list.get(i+1)));
        trace.setNote(list.get(i+2));
        return trace;
    }
    private List<String> validateExcel(List<List<String>> xlsList,Map<String,String> keyMap){
        List<String> showValiInfoList = new ArrayList<String>();
        StringBuilder showValiInfo = null;
        boolean addSb = false;
        Pattern pattern = Pattern.compile("^[0-9]{11}$");
        int i = 2;
        for(List<String> valiList:xlsList){
            showValiInfo = new StringBuilder("");
            showValiInfo.append("第");showValiInfo.append(i);showValiInfo.append("行");
            try {
                if(valiList.get(0) == null)continue;
                sdf.parse(valiList.get(0));
            }catch(Exception e) {
                showValiInfo.append(" A列日期格式有误");addSb = true;
            }
            if(!pattern.matcher(valiList.get(2)).matches()){
                showValiInfo.append(" C列电话号码格式有误");addSb = true;
            }
            if(keyMap.get(valiList.get(3)) == null){
                showValiInfo.append(" D列该学历在系统表中没有");addSb = true;
            }
            if(keyMap.get(valiList.get(4)) == null){
                showValiInfo.append(" E列该来源在系统表中没有");addSb = true;
            }
            if(keyMap.get(valiList.get(5)) == null){
                showValiInfo.append(" F列该来源在系统表中没有");addSb = true;
            }
            try {
                if(valiList.get(7)!=null||valiList.get(8)!=null)
                sdf.parse(valiList.get(6));
            }catch(Exception e) {
                showValiInfo.append(" G列日期格式有误");addSb = true;
            }
            if(valiList.get(7)==null&&(valiList.get(6)!=null||valiList.get(8)!=null)){
                showValiInfo.append(" H列不能为空");addSb = true;
            }else{
                if(valiList.get(7)!=null&&keyMap.get(valiList.get(7)) == null){
                    showValiInfo.append(" H列该情况在系统表中没有");addSb = true;
                }
            }
            try {
                if(valiList.get(10)!=null||valiList.get(11)!=null)
                sdf.parse(valiList.get(9));
            }catch(Exception e) {
                showValiInfo.append(" J列日期格式有误");addSb = true;
            }
            if(valiList.get(10)==null&&(valiList.get(9)!=null||valiList.get(11)!=null)){
                showValiInfo.append(" K列不能为空");addSb = true;
            }else{
                if(valiList.get(10)!=null&&keyMap.get(valiList.get(10)) == null){
                    showValiInfo.append(" K列该情况在系统表中没有");addSb = true;
                }
            }
            try {
                if(valiList.get(13)!=null||valiList.get(14)!=null)
                sdf.parse(valiList.get(12));
            }catch(Exception e) {
                showValiInfo.append(" M列日期格式有误");addSb = true;
            }
            if(valiList.get(13)==null&&(valiList.get(12)!=null||valiList.get(14)!=null)){
                showValiInfo.append(" N列不能为空");addSb = true;
            }else{
                if(valiList.get(13)!=null&&keyMap.get(valiList.get(13)) == null){
                    showValiInfo.append(" N列该情况在系统表中没有");addSb = true;
                }
            }
            if(addSb) showValiInfoList.add(showValiInfo.toString());i++;
        }
        return showValiInfoList;
    }
}
